The use of a database management system (DBMS) is well known in the art. Databases of the database management system are organized into tables that are comprised of rows and columns of data, wherein the rows are tuples and the columns are attributes. The tables are typically stored on direct access storage devices, such as magnetic or optical disk drives, for semi-permanent storage.
The use of a structured query language (SQL) interface to access data in the relational database management system is also well known in the art. The SQL interface has evolved into a standard language for database management system software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO). The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host languages such as C and COBOL.
An SQL procedure in a DBMS is created (or defined) by an SQL CREATE PROCEDURE statement. Different from an external stored procedure, an SQL procedure definition comprises a source code for the stored procedure. Users can change the procedure options, modify the procedure definition, and add a version or drop a version of the SQL procedure definition by executing, for example, an ALTER PROCEDURE statement. SQL procedures are stored in a database and maintained by the DBMS. In a manner similar to regular application programs, SQL procedures have a program life cycle comprising a design phase, an implementation phase, and a maintenance phase. SQL procedure developers follow a process of writing a code, testing the code, fixing bugs, etc., one or more times before the SQL procedures can be deployed to a production system for execution.
Typically, an internal implementation of an SQL procedure comprises a procedural logic component and a database request component. The procedural logic component comprises loop statements, conditional statements, and other control statements. The procedural logic component can be compiled into an internal representation of the procedural logic component for interpretation and execution during run time. Otherwise, the procedural logic component can be compiled into native code for direct execution. The database request component comprises SQL statements. The database request component is typically implemented via run-time instructions built by an SQL compiler and optimizer of the database requests by the DBMS.
Within many situations, users wish to ensure that performance of an SQL procedure deployed to a production system duplicates the behavior and performance of the SQL procedure on a testing system. Users cannot afford any minor behavior change in the procedural logic component after the SQL procedure is deployed. However, recreating the SQL procedure on the production system and generating a new internal representation of the SQL procedure engenders the possibility of behavior changes. A minor change in the procedural logic component can affect the main logic flow of the procedure, causing inaccurate results for SQL queries.
No conventional approach exists for ensuring consistent behavior and performance of an SQL procedure deployed from a source DBMS to a target DBMS. One related conventional technique has been implemented by DBMS users of external stored procedures. This conventional approach copies a procedure load module or DLL (dynamic load library) compiled on a testing system to a production system. However, this conventional technique is applicable only to external stored procedures, where the procedural logic component of the procedure is separate from the database request component and a load module or DLL is available to the user outside of the DBMS. An SQL procedure does not have an associated external load module; consequently, this conventional technique is not applicable.
What is therefore needed is a system, a computer program product, and an associated method for deploying an SQL procedure. The need for such a solution has heretofore remained unsatisfied.